﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace ADO_Performance_Test
{
    /// <summary>
    /// Demonstrates how to work with SqlCommand objects
    /// </summary>
    class SqlCommandDemo
    {
        SqlConnection conn;

        public SqlCommandDemo()
        {
            // Instantiate the connection
            conn = new SqlConnection(
               "Data Source=BHN-WIN\\SQLEXPRESS;Initial Catalog=PerformanceTestDB1;User ID=BHN-WIN\\kholub;Integrated Security=SSPI");
        }

        // call methods that demo SqlCommand capabilities
        static void Main()
        {
            SqlCommandDemo scd = new SqlCommandDemo();

            int[] tests = {
                              128,
                              256,
                              512,
                              1024,
                              2*1024,
                              4*1024,
                              8*1024
                             /* 16*1024,
                              32*1024,
                              64*1024,
                              128*1024,
                              256*1024,
                              512*1024,
                              1024*1024 */
                          };
#if false
            Console.WriteLine("Insert performance test1");
            Console.WriteLine("rows/bytes/milisec/k_insert_per_sec/KB_per_sec");
            for(int i = 0; i < tests.Length; i++)
                scd.InsertDataTest1(tests[i]);
            Console.WriteLine("Insert performance test1 finished");
            Console.WriteLine();
#endif
#if false
            Console.WriteLine("Insert performance test2");
            Console.WriteLine("rows/bytes/milisec/k_insert_per_sec/KB_per_sec");
            for (int i = 0; i < tests.Length; i++)
                scd.InsertDataTest3(tests[i]);
            Console.WriteLine("Insert performance test2 finished");
            Console.WriteLine();
#endif
#if false
            Console.WriteLine("Insert performance test3");
            Console.WriteLine("rows/bytes/milisec/k_insert_per_sec/KB_per_sec");
            for (int i = 0; i < tests.Length; i++)
                scd.InsertDataTest3(tests[i]);
            Console.WriteLine("Insert performance test3 finished");
            Console.WriteLine();
#endif   
            Console.WriteLine("Insert performance test4");
            Console.WriteLine("rows/bytes/milisec/k_insert_per_sec/KB_per_sec");
            for (int i = 0; i < tests.Length; i++)
                scd.InsertDataTest4(tests[i]);
            Console.WriteLine("Insert performance test4 finished");
            Console.WriteLine();

            Console.ReadKey();
        }

        /// <summary>
        /// use ExecuteReader method
        /// </summary>
        public void ReadData()
        {
            SqlDataReader rdr = null;

            try
            {
                // Open the connection
                conn.Open();

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

                // 2. Call Execute reader to get query results
                rdr = cmd.ExecuteReader();

                // print the CategoryName of each record
                while (rdr.Read())
                {
                    Console.WriteLine(rdr[0]);
                }
            }
            finally
            {
                // close the reader
                if (rdr != null)
                {
                    rdr.Close();
                }

                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// use ExecuteNonQuery method for Insert
        /// </summary>
        public void InsertDataTest1(int totalRows)
        {
            try
            {
                // Open the connection
                conn.Open();

                string deleteString = @"truncate table Test1";
                SqlCommand del_cmd = new SqlCommand(deleteString, conn);
                del_cmd.ExecuteNonQuery();

                // prepare command string
                string insertString = @"insert into Test1 (data) values ('123456789')";

                // 1. Instantiate a new command with a query and connection
                SqlTransaction t = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(insertString, conn, t);

                DateTime start = DateTime.Now;
                // 2. Call ExecuteNonQuery to send command
                for (int i = 0; i < totalRows; i++)
                {
                    cmd.ExecuteNonQuery();
                }
                t.Commit();
                DateTime end = DateTime.Now;
                TimeSpan s = end - start;
                
                //"rows/bytes/milisec/k_insert_per_sec/KB_per_sec"
                Console.WriteLine("{0} {1} {2} {3} {4}", totalRows, totalRows*4, s.TotalMilliseconds, totalRows/s.TotalMilliseconds, ((double)totalRows*4/(1024)) / (s.TotalMilliseconds / 1000));
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        
        public void InsertDataTest2(int totalRows)
        {
            try
            {
                // Open the connection
                conn.Open();

                string deleteString = @"truncate table Test2";
                SqlCommand del_cmd = new SqlCommand(deleteString, conn);
                del_cmd.ExecuteNonQuery();

                // prepare command string
                string insertString = @"insert into Test2 (data1, data2, data3, data4) values ('123456789', '123456789', '123456789', '123456789')";

                // 1. Instantiate a new command with a query and connection
                SqlTransaction t = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(insertString, conn, t);

                DateTime start = DateTime.Now;
                // 2. Call ExecuteNonQuery to send command
                for (int i = 0; i < totalRows; i++)
                {
                    cmd.ExecuteNonQuery();
                }
                t.Commit();
                DateTime end = DateTime.Now;
                TimeSpan s = end - start;

                //"rows/bytes/milisec/k_insert_per_sec/KB_per_sec"
                Console.WriteLine("{0} {1} {2} {3} {4}", totalRows, totalRows*4*4, s.TotalMilliseconds, (totalRows) / s.TotalMilliseconds, (((double)totalRows * 4 *4) / (1024)) / (s.TotalMilliseconds / 1000));
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public void InsertDataTest3(int totalRows)
        {
            try
            {
                // Open the connection
                conn.Open();

                string deleteString = @"truncate table Test3";
                SqlCommand del_cmd = new SqlCommand(deleteString, conn);
                del_cmd.ExecuteNonQuery();

                // prepare command string
                string insertString = @"insert into Test3 (data0, data1, data2, data3, 
                                                           data4, data5, data6, data7, 
                                                           data8, data9, data10, data11, 
                                                           data12, data13, data14, data15) 
                                                           values ('123456789', '123456789', '123456789', '123456789',
                                                                    '123456789', '123456789', '123456789', '123456789',
                                                                    '123456789', '123456789', '123456789', '123456789',
                                                                    '123456789', '123456789', '123456789', '123456789'
                                                                    )";

                // 1. Instantiate a new command with a query and connection
                SqlTransaction t = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(insertString, conn, t);

                DateTime start = DateTime.Now;
                // 2. Call ExecuteNonQuery to send command
                for (int i = 0; i < totalRows; i++)
                {
                    cmd.ExecuteNonQuery();
                }
                t.Commit();
                DateTime end = DateTime.Now;
                TimeSpan s = end - start;

                //"rows/bytes/milisec/k_insert_per_sec/KB_per_sec"
                Console.WriteLine("{0} {1} {2} {3} {4}", totalRows, totalRows * 4 * 16, s.TotalMilliseconds, (totalRows) / s.TotalMilliseconds, (((double)totalRows * 4 * 16) / (1024)) / (s.TotalMilliseconds / 1000));
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public void InsertDataTest4(int totalRows)
        {
            try
            {
                int stringSize = 512;
                // Open the connection
                conn.Open();

                string deleteString = @"truncate table Test4";
                SqlCommand del_cmd = new SqlCommand(deleteString, conn);
                del_cmd.ExecuteNonQuery();
                string testString = "";
                for (int i = 0; i < stringSize; i++)
                    testString += "a";

                // prepare command string
                string insertString = @"insert into Test4 (data) values ('" +testString+"')";
               // Console.WriteLine(insertString);

                // 1. Instantiate a new command with a query and connection
                SqlTransaction t = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(insertString, conn, t);

                DateTime start = DateTime.Now;
                // 2. Call ExecuteNonQuery to send command
                for (int i = 0; i < totalRows; i++)
                {
                    cmd.ExecuteNonQuery();
                }
                t.Commit();
                DateTime end = DateTime.Now;
                TimeSpan s = end - start;

                //"rows/bytes/milisec/k_insert_per_sec/KB_per_sec"
                Console.WriteLine("{0} {1} {2} {3} {4}", totalRows, totalRows * stringSize, s.TotalMilliseconds, totalRows / s.TotalMilliseconds, ((double)totalRows * stringSize / (1024)) / (s.TotalMilliseconds / 1000));
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// use ExecuteNonQuery method for Update
        /// </summary>
        public void UpdateData()
        {
            try
            {
                // Open the connection
                conn.Open();

                // prepare command string
                string updateString = @"
                 update Categories
                 set CategoryName = 'Other'
                 where CategoryName = 'Miscellaneous'";

                // 1. Instantiate a new command with command text only
                SqlCommand cmd = new SqlCommand(updateString);

                // 2. Set the Connection property
                cmd.Connection = conn;

                // 3. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// use ExecuteNonQuery method for Delete
        /// </summary>
        public void DeleteData()
        {
            try
            {
                // Open the connection
                conn.Open();

                // prepare command string
                string deleteString = @"delete from Test1 *";

                // 1. Instantiate a new command
                SqlCommand cmd = new SqlCommand();

                // 2. Set the CommandText property
                cmd.CommandText = deleteString;

                // 3. Set the Connection property
                cmd.Connection = conn;

                // 4. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// use ExecuteScalar method
        /// </summary>
        /// <returns>number of records</returns>
        public int GetNumberOfRecords()
        {
            int count = -1;

            try
            {
                // Open the connection
                conn.Open();

                // 1. Instantiate a new command
                SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);

                // 2. Call ExecuteScalar to send command
                count = (int)cmd.ExecuteScalar();
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return count;
        }
    }
}


 
